﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using Common;

namespace AccessToDataLibrary
{
    public partial class AccessToDataLibrary
    {

        public List<Book> GetAllBooks()
        {
            if (this.libraryDataSet == null)
                return null;
            //DataRowCollection searchedRow = this.libraryDataSet.Books.Rows;

            DataRow[] searchedRow = this.libraryDataSet.Books.Select();


            return this.GetRestPartsOfBook(searchedRow);
        }

        private List<Book> GetRestPartsOfBook (DataRowCollection searchedRows)
        {
            if (searchedRows == null || searchedRows.Count == 0)
                return null;
            List<Book> books = new List<Book>();

            foreach (UniversityLibraryDataSet.BooksRow bookRow in searchedRows)
            {
                Book book = new Book();
                book.ISBN = bookRow.ISBN;
                

                if (bookRow.ItemsRow != null)
                {
                    book.ID = bookRow.ItemsRow.ID;
                    book.Name = bookRow.ItemsRow.Name;
                    book.Publisher = bookRow.ItemsRow.Publisher;
                    book.PublishedDate = bookRow.ItemsRow.PublishedDate;

                }
                else
                {
                    throw new Exception("Cannot find corresponding item.");
                }
                if (bookRow.AuthorsRow != null)
                {
                    book.AuthorName = bookRow.AuthorsRow.Name;
                }
                else
                {
                    throw new Exception("Cannot find corresponding authors.");
                }

                books.Add(book);
            }
            return books;
        }
        private List<Book> GetRestPartsOfBook(DataRow[] searchedRows)
        {
            if (searchedRows == null || searchedRows.Length == 0)
            {
                return null;
            }
            List<Book> books = new List<Book>();

            foreach (UniversityLibraryDataSet.BooksRow bookRow in searchedRows)
            {
                Book book = new Book();
                book.ISBN = bookRow.ISBN;

                /*DataRow[] itemRows = this.libraryDataSet.Items.Select("ID = "+bookRow.ItemId);

                if (itemRows != null && itemRows.Length != 0)
                {
                    UniversityLibraryDataSet.ItemsRow itemRow = (UniversityLibraryDataSet.ItemsRow)itemRows[0];
                    book.ID = itemRow.ID;
                    book.Name = itemRow.Name ;
                    book.Publisher = itemRow.Publisher;
                    book.PublishedDate = itemRow.PublishedDate;
                }
                */
                if (bookRow.ItemsRow != null)
                {
                    book.ID = bookRow.ItemsRow.ID;
                    book.Name = bookRow.ItemsRow.Name;
                    book.Publisher = bookRow.ItemsRow.Publisher;
                    book.PublishedDate = bookRow.ItemsRow.PublishedDate;
                }
                else
                {
                    throw new Exception("Cannot find corresponding item.");
                }
                if (bookRow.AuthorsRow != null)
                {
                    book.AuthorName = bookRow.AuthorsRow.Name;
                }
                else
                {
                    throw new Exception("Cannot find corresponding authors.");
                }

                books.Add(book);
            }

            return books;
        }      
        public List<Book> GetSearchedBooks(Book searchedBook)
        {
            List<Book> books = new List<Book>();
            string filter = string.Empty;
            if (searchedBook.PublishedDate != DateTime.MinValue)
            {
                filter = "Parent(FK_Books_Items).Name Like '%" + searchedBook.Name + "%'" +
                   "AND Parent(FK_Books_Authors).Name Like '%" + searchedBook.AuthorName + "%'" +
                   "AND Parent(FK_Books_Items).Publisher Like '%" + searchedBook.Publisher + "%'" +
                   "AND Convert(Parent(FK_Books_Items).PublishedDate, 'System.String') Like '%" + searchedBook.PublishedDate + "%'";
            }
            else
            {
                filter = "Parent(FK_Books_Items).Name Like '%" + searchedBook.Name + "%'" +
                   "AND Parent(FK_Books_Authors).Name Like '%" + searchedBook.AuthorName + "%'" +
                   "AND Parent(FK_Books_Items).Publisher Like '%" + searchedBook.Publisher + "%'";
            }
            DataRow[] searchedBooks = libraryDataSet.Books.Select(filter);
            foreach (UniversityLibraryDataSet.BooksRow book in searchedBooks)
            {
                books.Add(new Book(
                    book.ItemsRow.Name,
                    book.ItemsRow.Publisher,
                    book.ItemsRow.PublishedDate,
                    book.ISBN,
                    book.AuthorsRow.Name));
            }
            return books;
          /*  //получаем набор найденных строк из таблицы Items
            UniversityLibraryDataSet.ItemsRow[] searchedItemRows =
                (UniversityLibraryDataSet.ItemsRow[])libraryDataSet.Items.Select(MakeFilteredQuery(searchedBook.ItemFields));

            // получаем набор найденных строк из таблицы Authors
            UniversityLibraryDataSet.AuthorsRow[] searchedAuthorsRows =
                (UniversityLibraryDataSet.AuthorsRow[])libraryDataSet.Authors.Select(MakeFilteredQuery(searchedBook.AuthorFields));
            // искать строки в таблице Books не стоит, поскольку поиск по полю ISBN  не производится, а это поля находится именно в таблице Books

            if (
                (searchedItemRows == null || searchedItemRows.Length == 0) ||
                (searchedAuthorsRows == null || searchedAuthorsRows.Length == 0)
                )
            {
                return new List<Book>(0);
            }

            //создаём списки для набор строк Items и Books для более гибкого манипулирования элементами массива
            List<UniversityLibraryDataSet.ItemsRow> searchedItemsRowsList = searchedItemRows.ToList<UniversityLibraryDataSet.ItemsRow>();
            List<UniversityLibraryDataSet.BooksRow> searhedBooksRowsList = new List<UniversityLibraryDataSet.BooksRow>();

            //заполняем список книг. Для каждой записи в таблице Items существует всего одна запись в таблице Books.
            foreach (UniversityLibraryDataSet.ItemsRow itemRow in searchedItemsRowsList)
            {
                UniversityLibraryDataSet.BooksRow[] bookRow = itemRow.GetBooksRows();
                if (bookRow != null && bookRow.Length != 0)
                    searhedBooksRowsList.Add(bookRow[0]);
            }

            //конечный набор искомых строк
            List<UniversityLibraryDataSet.BooksRow> realBooksRows = new List<UniversityLibraryDataSet.BooksRow>();

            //выбираем те книги, которые написаны данным автором
            for (int i = 0; i < searchedAuthorsRows.Length; i++)
            {
                List<UniversityLibraryDataSet.BooksRow> tempSearched =
                    searhedBooksRowsList.FindAll(
                        val => val.AuthorID == searchedAuthorsRows[i].ID);
                realBooksRows.AddRange(tempSearched);
            }

            if (realBooksRows.Count == 0)
            {
                return new List<Book>(0);
            }
            return GetRestPartsOfBook(realBooksRows.ToArray());
            */
        }
        public void AddBook(List<Book> book_)
        {
            DataRow authorDataRow;

            foreach (Book book in book_)
            {
                bool canAdd = IsUniqueBookInDB(book, out authorDataRow, LibraryAction.AddBook);
                if (!canAdd)
                    continue;

                UniversityLibraryDataSet.ItemsRow itemRow =
                    libraryDataSet.Items.AddItemsRow(Guid.NewGuid(), book.Name, book.Publisher, book.PublishedDate);
                UniversityLibraryDataSet.AuthorsRow authorRow;
                // если такого автора ещё не  в БД
                if (authorDataRow == null)
                    authorRow = libraryDataSet.Authors.AddAuthorsRow(Guid.NewGuid(), book.AuthorName);
                else
                    authorRow = (UniversityLibraryDataSet.AuthorsRow)authorDataRow;

                libraryDataSet.Books.AddBooksRow(itemRow, authorRow, book.ISBN);
            }

            provider.UpdateAllData();

        }
        private bool IsUniqueBookInDB (Book book, out DataRow authorRow, LibraryAction action)
        {
            authorRow = null;

            DataRow[] bookRows = libraryDataSet.Books.Select("[ISBN] = '"+ book.ISBN+"'");

            if (action == LibraryAction.AddBook)
            {
                if (bookRows != null && bookRows.Length > 0)
                {
                    return false;
                }
            }
            else if (action == LibraryAction.UpdateBook)
            {
                if (bookRows != null && bookRows.Length > 0)
                {
                    foreach (UniversityLibraryDataSet.BooksRow bookRow in bookRows)
                    {
                        if (bookRow.ItemId != book.ID)
                        {
                            return false;
                        }
                    }
                }
            }
            else return false;

            DataRow[] authorsRows = 
                libraryDataSet.Authors.Select("Name = '"+book.AuthorName+"'");
            if (authorsRows != null && authorsRows.Length > 0)
            {
                authorRow = authorsRows[0];
                //получаем все книги написанные данным автором
                foreach (DataRow authorR in authorsRows)
                {
                    UniversityLibraryDataSet.BooksRow[] booksRow =
                        ((UniversityLibraryDataSet.AuthorsRow)authorR).GetBooksRows();
                    foreach (UniversityLibraryDataSet.BooksRow bookR in booksRow)
                    {
                        if (book.ID != bookR.ItemId)
                        {
                            if (book.Name == bookR.ItemsRow.Name)
                            {
                                return false;
                            }
                        }
                    }
                }
            }
            return true;
        }
        public void UpdateBooks(List<Book> book_)
        {
            DataRow author;

            foreach (Book book in book_)
            {
                bool canUpdate = IsUniqueBookInDB(book, out author, LibraryAction.UpdateBook);
                if (!canUpdate)
                    continue;

                UniversityLibraryDataSet.ItemsRow itemRow = libraryDataSet.Items.FindByID(book.ID);
                itemRow.Name = book.Name;
                itemRow.Publisher = book.Publisher;
                itemRow.PublishedDate = book.PublishedDate;

                UniversityLibraryDataSet.AuthorsRow authorRow;
                if (author == null)
                    authorRow = libraryDataSet.Authors.AddAuthorsRow(Guid.NewGuid(), book.AuthorName);
                else
                {
                    authorRow = (UniversityLibraryDataSet.AuthorsRow)author;
                }

                UniversityLibraryDataSet.BooksRow bookRow = libraryDataSet.Books.FindByItemId(book.ID);
                bookRow.ISBN = book.ISBN;
                bookRow.AuthorID = authorRow.ID;
            }

            provider.UpdateAllData();
        }
        public List<Copie> GetBookCopies(Guid bookID, out string bookName)
        {
            DataRow[] bookCopies = libraryDataSet.Copies.Select("ItemID = '"+bookID+"'");
            bookName = libraryDataSet.Items.FindByID(bookID).Name;

            List<Copie> copies = new List<Copie>();
            foreach(UniversityLibraryDataSet.CopiesRow singleCop in bookCopies)
            {
                bool isBorrowed = false;
                //DataRow borrowed =libraryDataSet.Copies.Rows.Find(singleCop.ID);
                //DataRow[] borrowed = singleCop.GetBorrowsRows();
                DataRow[] borrowed= singleCop.GetChildRows("FK_Borrows_Copies");
                if (borrowed != null && borrowed.Length > 0)
                {
                    isBorrowed = true;
                }

                copies.Add(new Copie(singleCop.ID,singleCop.ItemID, isBorrowed));
            }
            return copies;
        }
        public bool BorrowingBook (List<BorrowedCopie> borCops)
        {
            try
            {
                foreach (BorrowedCopie borCop in borCops)
                {
                    libraryDataSet.Borrows.AddBorrowsRow(libraryDataSet.Copies.FindByID(borCop.CopyID), borCop.BorrowedDate,
                        libraryDataSet.Users.FindByID(borCop.UserID));
                }

                provider.UpdateAllData(); 
            }
            catch
            {
                return false;
            }
            return true;
        }
    }
}
